﻿using Microsoft.Xrm.Sdk.Metadata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Project.Co_DataTransformationPrototype
{
    public sealed class MappingUtilities
    {
        private static MappingUtilities instance;
        private StringBuilder scriptCollect;

        private MappingUtilities()
        {
            //scriptCollect = new StringBuilder();
        }

        public static MappingUtilities GetInstance()
        {
            if (instance == null)
                instance = new MappingUtilities();

            return instance;
        }

        //#region Implementation for the data transformation script generation

        //#region Event to handle updated script content
        //public event EventHandler<ScriptEventArgs> ScriptUpdated;

        //private void OnScriptUpdated()
        //{
        //    EventHandler<ScriptEventArgs> handler = ScriptUpdated;
        //    if (handler != null)
        //    {
        //        ScriptEventArgs e = new ScriptEventArgs();
        //        e.scriptCollection = scriptCollect;
        //        handler(this, e);
        //    }
        //}
        //#endregion

        public StringBuilder initializeMappingGeneration(IDictionary<Guid, ElementEventInfo> eventCollection)
        {
            scriptCollect = new StringBuilder(); // TODO: Implement More efficient way which uses boolead 'isProccessed' to avoid redundant sql statements

            foreach (KeyValuePair<Guid, ElementEventInfo> item in eventCollection)
            {
                scriptCollect.Append(item.Value.collectSQLStatements());
            }
            //OnScriptUpdated();

            return scriptCollect;
        }
        //#endregion
    }

    internal static class MappingJoinCondition
    {
        /// <summary>
        /// Returns the names of the attributes at each end of a link between the two given entities
        /// </summary>
        /// <param name="sourceEntity"></param>
        /// <param name="targetEntity"></param>
        /// <returns></returns>
        internal static IList<string> findMatchingIDAttributes(Entity sourceEntity, Entity targetEntity)
        {
            IList<string> attributeList = new List<string>();

            // TODO: what happens if there's more than one link?

            // Find the relationship between the two entities
            EntityAssociation link = EntityAssociation.GetLink(sourceEntity, targetEntity);
            if (link != null)
            {
                attributeList.Add(link.AttributeInSource);
                attributeList.Add(link.AttributeInTarget);
            }
            else
            {
                link = EntityAssociation.GetLink(targetEntity, sourceEntity);
                if (link != null)
                {
                    attributeList.Add(link.AttributeInTarget);
                    attributeList.Add(link.AttributeInSource);
                }
            }

            return attributeList;
        }
    }

    /// <summary>
    /// Provides internal static methods that translate attribute types into its Sql data type definition
    /// </summary>
    internal static class MappingTypeConvertion
    {
        /// <summary>
        /// Translates the given Sql data type into its equivalent sql-statement
        /// </summary>
        /// <param name="sqlType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        internal static string translateDataTypeIntoSQLString(SqlDbType sqlType, string size = "255")
        {
            string strSqlCode = string.Empty;

            switch (sqlType)
            {
                case SqlDbType.BigInt:
                    strSqlCode = string.Format(@" BIGINT"); break;

                case SqlDbType.Binary:
                    strSqlCode = string.Format(@" BINARY ({0})", size); break;
                case SqlDbType.Image:
                    strSqlCode = string.Format(@" IMAGE"); break;
                case SqlDbType.Timestamp:
                    strSqlCode = string.Format(@" TIMESTAMP ({0})", size); break;
                case SqlDbType.VarBinary:
                    strSqlCode = string.Format(@" VARBINARY ({0})", size); break;

                case SqlDbType.Bit:
                    strSqlCode = string.Format(@" BIT"); break;

                case SqlDbType.Char:
                    strSqlCode = string.Format(@" CHAR ({0})", size); break;
                case SqlDbType.NChar:
                    strSqlCode = string.Format(@" NCHAR ({0})", size); break;
                case SqlDbType.VarChar:
                    strSqlCode = string.Format(@" VARCHAR ({0})", size); break;
                case SqlDbType.NVarChar:
                    strSqlCode = string.Format(@" NVARCHAR ({0})", size); break;
                case SqlDbType.Text:
                    strSqlCode = string.Format(@" TEXT"); break;
                case SqlDbType.NText:
                    strSqlCode = string.Format(@" NTEXT"); break;
                case SqlDbType.Xml:
                    strSqlCode = string.Format(@" XML"); break;

                case SqlDbType.DateTime:
                    strSqlCode = string.Format(@" DATETIME"); break;
                case SqlDbType.SmallDateTime:
                    strSqlCode = string.Format(@" SMALLDATETIME"); break;
                case SqlDbType.DateTime2:
                    strSqlCode = string.Format(@" DATETIME2", size); break;
                case SqlDbType.DateTimeOffset:
                    strSqlCode = string.Format(@" DATETIMEOFFSET"); break;
                case SqlDbType.Date:
                    strSqlCode = string.Format(@" DATE"); break;
                case SqlDbType.Time:
                    strSqlCode = string.Format(@" TIME"); break;

                case SqlDbType.Decimal:
                    strSqlCode = string.Format(@" DECIMAL ({0}, {1})", size, 2); break;
                case SqlDbType.Money:
                    strSqlCode = string.Format(@" MONEY"); break;
                case SqlDbType.SmallMoney:
                    strSqlCode = string.Format(@" SMALLMONEY", size); break;

                case SqlDbType.Float:
                    strSqlCode = string.Format(@" FLOAT"); break;
                case SqlDbType.Int:
                    strSqlCode = string.Format(@" INT"); break;
                case SqlDbType.Real:
                    strSqlCode = string.Format(@" REAL"); break;
                case SqlDbType.UniqueIdentifier:
                    strSqlCode = string.Format(@" UNIQUEIDENTIFIER"); break;
                case SqlDbType.SmallInt:
                    strSqlCode = string.Format(@" SMALLINT"); break;
                case SqlDbType.TinyInt:
                    strSqlCode = string.Format(@" TINYINT"); break;

                case SqlDbType.Variant:
                case SqlDbType.Udt:
                    strSqlCode = string.Format(@" SQL_VARIANT"); break;

                case SqlDbType.Structured:
                    strSqlCode = string.Format(@" STRUCTURED"); break;

                default:
                    throw new ArgumentOutOfRangeException("sqlType");
            }
            return strSqlCode;
        }

        /// <summary>
        /// Translates CRM attribute type into an appropriate SqlDb type
        /// Note: Not fully implemented yet
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        internal static SqlDbType translateCRMDataTypeIntoSQLDbType(AttributeTypeCode type)
        {
            SqlDbType result = SqlDbType.NVarChar;

            switch (type)
            {
                case AttributeTypeCode.BigInt:
                    result = SqlDbType.BigInt; break;
                case AttributeTypeCode.Boolean:
                    result = SqlDbType.Bit; break;
                case AttributeTypeCode.DateTime:
                    result = SqlDbType.DateTime; break;
                case AttributeTypeCode.Decimal:
                    result = SqlDbType.Decimal; break;
                case AttributeTypeCode.Double:
                    result = SqlDbType.Float; break;
                case AttributeTypeCode.Integer:
                    result = SqlDbType.Int; break;
                case AttributeTypeCode.Lookup:
                    result = SqlDbType.UniqueIdentifier; break;
                case AttributeTypeCode.Money:
                    result = SqlDbType.Money; break;
                case AttributeTypeCode.Owner:
                    result = SqlDbType.UniqueIdentifier; break;
                case AttributeTypeCode.String:
                    break;
                case AttributeTypeCode.Uniqueidentifier:
                    result = SqlDbType.UniqueIdentifier; break;

                case AttributeTypeCode.CalendarRules:
                case AttributeTypeCode.Customer:
                case AttributeTypeCode.EntityName:
                case AttributeTypeCode.ManagedProperty:
                case AttributeTypeCode.Memo:
                case AttributeTypeCode.PartyList:
                case AttributeTypeCode.Picklist:
                case AttributeTypeCode.State:
                case AttributeTypeCode.Status:
                case AttributeTypeCode.Virtual:
                    break;

                default:
                    new ArgumentOutOfRangeException("CRMAttributeType"); break;
            }

            return result;
        }

        /// <summary>
        /// Returns the SQL statements which will convert the given attribute from its old data type to the new one
        /// </summary>
        /// <param name="parentEntity"></param>
        /// <param name="attribute"></param>
        /// <param name="oldType"></param>
        /// <param name="newType"></param>
        /// <returns></returns>
        internal static string getSQLStringForDataTypeConversion(Entity parentEntity, EntityAttribute attribute, SqlDbType oldType, SqlDbType newType)
        {
            string strPrefix = "temp_";
            bool hasImplicitConversion = false;
            bool hasExplicitConversion = false;
            StringBuilder strbSqlCode = new StringBuilder();

            #region Binary Conversion Strategy
            if (oldType.Equals(SqlDbType.Binary))
            {
                if (newType.Equals(SqlDbType.Float) || newType.Equals(SqlDbType.Real))
                {
                    // Copy data over to temp column, while converting it to the proper type (ELT)
                    strbSqlCode.AppendFormat(@"ALTER TABLE [{0}].{1} ADD {2} {3}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, strPrefix + attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(newType));
                    strbSqlCode.AppendFormat(@"UPDATE [{0}].{1} SET" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    //set new_column_name = convert(decimal(18,2), old_column_name)
                    strbSqlCode.AppendFormat(@"    {0} = cast(convert({1}, {2}) as {3})" + Environment.NewLine, strPrefix + attribute.CurrentName, "int", attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(newType));

                    //
                    strbSqlCode.AppendFormat("ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strbSqlCode.AppendFormat("    DROP COLUMN {0}" + Environment.NewLine, attribute.CurrentName);
                    strbSqlCode.AppendLine("GO" + Environment.NewLine);

                    strbSqlCode.AppendFormat("EXEC sp_RENAME '{0}.{1}.{2}', '{3}', 'COLUMN' " + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, strPrefix + attribute.CurrentName, attribute.CurrentName);
                    strbSqlCode.AppendLine("GO");
                }
                else
                {
                    hasImplicitConversion = true;
                }
            }
            #endregion
            #region Char, NChar, VarChar & nVarChar Conversion Strategy
            else if (oldType.Equals(SqlDbType.Char) || oldType.Equals(SqlDbType.VarChar) || oldType.Equals(SqlDbType.NChar) || oldType.Equals(SqlDbType.NVarChar))
            {
                if (newType.Equals(SqlDbType.Binary) || newType.Equals(SqlDbType.VarBinary) || newType.Equals(SqlDbType.Timestamp))
                    hasExplicitConversion = true;
                else
                    hasImplicitConversion = true;
            }
            #endregion
            #region DateTime & SmallDateTime Conversion Strategy
            else if (oldType.Equals(SqlDbType.DateTime) || oldType.Equals(SqlDbType.SmallDateTime))
            {
                if (newType.Equals(SqlDbType.Binary) || newType.Equals(SqlDbType.VarBinary) || newType.Equals(SqlDbType.Decimal) ||
                    newType.Equals(SqlDbType.Float) || newType.Equals(SqlDbType.Real) || newType.Equals(SqlDbType.BigInt) ||
                    newType.Equals(SqlDbType.Int) || newType.Equals(SqlDbType.SmallInt) || newType.Equals(SqlDbType.TinyInt) ||
                    newType.Equals(SqlDbType.Money) || newType.Equals(SqlDbType.SmallMoney) || newType.Equals(SqlDbType.Bit) ||
                    newType.Equals(SqlDbType.Timestamp))
                {
                    hasExplicitConversion = true;
                }
                else
                {
                    hasImplicitConversion = true;
                }
            }
            #endregion
            #region Date, Time, DateTimeOffSet & DateTime2  Conversion Strategy
            else if (oldType.Equals(SqlDbType.Date) || oldType.Equals(SqlDbType.Time) || oldType.Equals(SqlDbType.DateTimeOffset) || oldType.Equals(SqlDbType.DateTime2))
            {
                if (newType.Equals(SqlDbType.Binary) || newType.Equals(SqlDbType.VarBinary))
                    hasExplicitConversion = true;
                else
                    hasImplicitConversion = true;
            }
            #endregion
            #region Decimal Conversion Strategy
            else if (oldType.Equals(SqlDbType.Decimal))
            {
                if (newType.Equals(SqlDbType.Decimal))
                    hasExplicitConversion = true; // prevent precision or scale
                else
                    hasImplicitConversion = true;
            }
            #endregion
            #region BigInt & Int Conversion Strategy
            else if (oldType.Equals(SqlDbType.BigInt) || oldType.Equals(SqlDbType.Int))
            {
                if (newType.Equals(SqlDbType.UniqueIdentifier))
                {
                    // Create temp column
                    strbSqlCode.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strbSqlCode.AppendFormat(@"    ADD {0}{1} {2} NULL" + Environment.NewLine, strPrefix, attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(SqlDbType.UniqueIdentifier));
                    strbSqlCode.AppendLine(@"GO");

                    // Store existing data in new column (ETL)
                    strbSqlCode.AppendFormat(@"IF OBJECT_ID (N'{0}.getGUID', N'FN') IS NOT NULL" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME);
                    strbSqlCode.AppendFormat(@"    DROP FUNCTION {0}.getGUID;" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME);
                    strbSqlCode.AppendLine("GO");
                    strbSqlCode.AppendFormat(@"CREATE FUNCTION {0}.getGUID (@input varchar(50))", Data.DatabaseSettings.SHEMA_NAME);
                    strbSqlCode.AppendLine(@"RETURNS uniqueidentifier");
                    strbSqlCode.AppendLine(@"AS" + Environment.NewLine + "BEGIN");
                    strbSqlCode.AppendLine(@"    DECLARE @uuid varchar(50)");
                    strbSqlCode.AppendLine(@"    SET @uuid = RIGHT(REPLICATE('0',32) + @input, 32)");
                    strbSqlCode.AppendLine(@"    return (SELECT  CAST(");
                    strbSqlCode.AppendLine(@"			SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +");
                    strbSqlCode.AppendLine(@"			SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)");
                    strbSqlCode.AppendLine(@"    AS UNIQUEIDENTIFIER));");
                    strbSqlCode.AppendLine(@"END;" + Environment.NewLine + "GO");
                    strbSqlCode.AppendFormat(@"UPDATE [{0}].{1} SET" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strbSqlCode.AppendFormat(@"    temp_{0} = {1}.getGUID(cast({0} as varchar(50)))" + Environment.NewLine, attribute.CurrentName, Data.DatabaseSettings.SHEMA_NAME);
                    strbSqlCode.AppendLine("GO");

                    // Remove old column and rename temp_column
                    strbSqlCode.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                    strbSqlCode.AppendFormat(@"    DROP COLUMN {0}" + Environment.NewLine, attribute.CurrentName);
                    strbSqlCode.AppendLine("GO");
                    strbSqlCode.AppendFormat("EXEC sp_RENAME '{0}.{1}.{2}', '{3}', 'COLUMN' " + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, strPrefix + attribute.CurrentName, attribute.CurrentName);
                    strbSqlCode.AppendLine("GO" + Environment.NewLine);
                }
                else
                {
                    hasImplicitConversion = true;
                }
            }
            #endregion
            #region Variant Conversion Strategy
            else if (oldType.Equals(SqlDbType.Variant))
            {
                hasExplicitConversion = true;
            }
            #endregion
            #region XML Conversion Strategy
            else if (oldType.Equals(SqlDbType.Xml))
            {
                if (newType.Equals(SqlDbType.Binary) || newType.Equals(SqlDbType.VarBinary) || newType.Equals(SqlDbType.Char) ||
                    newType.Equals(SqlDbType.VarChar) || newType.Equals(SqlDbType.NChar) || newType.Equals(SqlDbType.NVarChar) ||
                    newType.Equals(SqlDbType.Xml))
                    hasExplicitConversion = true;
                else
                    hasImplicitConversion = true;
            }
            #endregion
            #region UDT Conversion Strategy
            else if (oldType.Equals(SqlDbType.Udt))
            {
                if (newType.Equals(SqlDbType.Binary) || newType.Equals(SqlDbType.VarBinary) || newType.Equals(SqlDbType.Char) ||
                    newType.Equals(SqlDbType.VarChar) || newType.Equals(SqlDbType.NChar) || newType.Equals(SqlDbType.NVarChar))
                    hasExplicitConversion = true;
                else
                    hasImplicitConversion = true;
            }
            #endregion
            else
            {
                hasImplicitConversion = true;
            }

            // Implicit conversion of data type
            if (hasImplicitConversion)
            {
                strbSqlCode.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                strbSqlCode.AppendFormat(@"    ALTER COLUMN {0} {1}" + Environment.NewLine, attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(newType));
                strbSqlCode.AppendLine("GO");
            }
            else if (hasExplicitConversion)
            {
                // Copy data over to temp column, while converting it to the proper type (ELT)
                strbSqlCode.AppendFormat(@"ALTER TABLE [{0}].{1} ADD {2} {3}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, strPrefix + attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(newType));
                strbSqlCode.AppendFormat(@"UPDATE [{0}].{1} SET" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                //set new_column_name = convert(decimal(18,2), old_column_name)
                strbSqlCode.AppendFormat(@"    {0} = cast({1} as {2})" + Environment.NewLine, strPrefix + attribute.CurrentName, attribute.CurrentName, MappingTypeConvertion.translateDataTypeIntoSQLString(newType));
                strbSqlCode.AppendLine("GO" + Environment.NewLine);
                //
                strbSqlCode.AppendFormat("ALTER TABLE [{0}].{1}" + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName);
                strbSqlCode.AppendFormat("    DROP COLUMN {0}" + Environment.NewLine, attribute.CurrentName);
                strbSqlCode.AppendLine("GO" + Environment.NewLine);

                strbSqlCode.AppendFormat("EXEC sp_RENAME '{0}.{1}.{2}', '{3}', 'COLUMN' " + Environment.NewLine, Data.DatabaseSettings.SHEMA_NAME, parentEntity.CurrentName, strPrefix + attribute.CurrentName, attribute.CurrentName);
                strbSqlCode.AppendLine("GO");
            }

            return strbSqlCode.ToString();
        }
    }
}
